﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;

namespace ProjectSchedule.Common
{
    public static class ExcelExtensions
    {
        /// <summary>
        /// 扩展方法 Clear Region cells on the sheet
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="region"></param>
        public static void ClearCells(this ISheet sheet, CellRangeAddress region)
        {
            for (int i = region.FirstRow; i <= region.LastRow; i++)
            {
                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                {
                    sheet.GetRow(i).GetCell(j).CellFormula = String.Empty;
                    sheet.GetRow(i).GetCell(j).SetCellValue(String.Empty);
                }
            }
        }

        /// <summary>
        /// 扩展方法 Set a format string value for the cell
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="args"></param>
        public static void SetCellFormatValue(this ICell cell, params object[] args)
        {
            cell.SetCellValue(String.Format(cell.ToString(), args));
        }

        public static void SetCellStyle(this IRow row, ICellStyle style, int index)
        {
            for (int i = 0; i < 6; i++)
            {
                row.CreateCell(i + index).SetCellStyle(style, false);
            }
        }

        public static ICell SetCellDateStyle(this ICell cell, ICellStyle style, short format = -1)
        {
            if (format != -1)
            {
                style.DataFormat = format;
            }
            cell.CellStyle = style;

            return cell;
        }

        public static ICell SetCellStyle(this ICell cell,ICellStyle style, bool setColor, short dateFormat = -1)
        {
            style.BorderBottom = BorderStyle.Thin;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.BorderLeft = BorderStyle.Thin;
            style.LeftBorderColor = HSSFColor.Black.Index;
            style.BorderRight = BorderStyle.Thin;
            style.RightBorderColor = HSSFColor.Black.Index;
            style.BorderTop = BorderStyle.Thin;
            style.TopBorderColor = HSSFColor.Black.Index;

            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            if (setColor)
            {
                style.FillForegroundColor = HSSFColor.Yellow.Index;
                style.FillPattern = FillPattern.BigSpots;
                style.FillBackgroundColor = HSSFColor.Green.Index;

                style.FillBackgroundColor = HSSFColor.Yellow.Index;
            }
            if (dateFormat != -1)
            {
                style.DataFormat = dateFormat;
            }
            cell.CellStyle = style;
            return cell;
        }

        public static ICell SetTitleStyle(this ICell cell, ICellStyle style, IFont font)
        {
            font.FontName = "宋体";
            font.FontHeightInPoints = 16;
            style.SetFont(font);
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            cell.CellStyle = style;
            return cell;
        }

        public static string GetStringCellValue(this IRow row, int index)
        {
            return GetCellValue<string>(row, index);
        }

        public static double GetNumericCellValue(this IRow row, int index)
        {
            return GetCellValue<double>(row, index);
        }

        public static bool GetBooleanCellValue(this IRow row, int index)
        {
            return GetCellValue<bool>(row, index);
        }

        public static DateTime GetDateCellValue(this IRow row, int index)
        {
            return GetCellValue<DateTime>(row, index);
        }

        private static T GetCellValue<T>(this IRow row, int index)
        {
            object value = typeof(T).IsValueType ? Activator.CreateInstance(typeof(T)) : null;
            try
            {
                if (row != null && row.LastCellNum > index)
                {
                    var cellType = row.Cells[index].CellType;
                    if (typeof(T).Equals(typeof(String)) && cellType == CellType.String)
                    {
                        value = row.Cells[index].StringCellValue ?? String.Empty;
                    }
                    else if (typeof(T).Equals(typeof(Double)) && cellType == CellType.Numeric)
                    {
                        value = row.Cells[index].NumericCellValue;
                    }
                    else if (typeof(T).Equals(typeof(DateTime)) && cellType == CellType.Numeric)
                    {
                        value = row.Cells[index].DateCellValue;
                    }
                    else if (typeof(T).Equals(typeof(Boolean)) && cellType == CellType.Boolean)
                    {
                        value = row.Cells[index].BooleanCellValue;
                    }
                    else if (cellType != CellType.Blank && cellType != CellType.Error)
                    {
                        string v = row.Cells[index].ToString();
                        if (!typeof(T).Equals(typeof(String)))
                        {
                            value = Convert.ChangeType(v, typeof(T));
                        }
                        else
                        {
                            value = v;
                        }
                    }
                }
            }
            catch (Exception)
            {
                var obj = new object();
            }
            return (T)value;
        }
    }
}
